Showing posts with label DDL commands in SQL Server with examples. Show all posts
Showing posts with label DDL commands in SQL Server with examples. Show all posts

DDL commands in SQL Server with examples

DDL (Data Definition Language): 

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database.

Examples of DDL commands:


CREATE - is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

Syntax of table creation:

CREATE TABLE [SCHEMA].TABLE_NAME(COLUMN_NAME COLUMN_DATATYPE)

Example of table creation:

CREATE TABLE dbo.Students(Id INT IDENTITY(1,1) PRIMARY KEY, age INT, gender CHAR(1))
GO

Syntax of view creation:

CREATE VIEW [SCHEMA].VIEW_NAME
AS
SELECT_STATEMENT

Example of view creation:

CREATE VIEW dbo.Students_VW
AS
SELECT
  Id,
  age,
  gender
FROM
  Students
GO


DROP - is used to delete objects from the database.

Syntax of dropping a table:

DROP TABLE [SCHEMA].TABLE_NAME

Example of dropping a table:

DROP TABLE dbo.Students


ALTER - is used to alter the structure of the database.

Syntax of altering size of a column in table:

ALTER TABLE [SCHEMA].TABLE_NAME
ALTER COLUMN COLUMN_NAME DATA_TYPE(DATA_LENGTH)

Example of altering a column size of a table:

ALTER TABLE dbo.Students
ALTER COLUMN gender VARCHAR(20)

Syntax of adding a new column in a table:

ALTER TABLE [SCHEMA].TABLE_NAME
ADD COLUMN_NAME DATA_TYPE(DATA_LENGTH)

Example of adding a new column in a table:

ALTER TABLE dbo.Students
ADD salary float


TRUNCATE - is used to remove all records from a table, including all spaces allocated for the records are removed.

Syntax of truncating a table:

TRUNCATE TABLE [SCHEMA].TABLE_NAME

Example of truncating a table:

TRUNCATE TABLE dbo.Students


Conclusion: 

So today we saw the SQL DDL Commands that we use very frequently while writing queries in SQL Server.



Followers

Popular Posts